#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
my ($cmd);
use db_parameters;
use ensembl_parameters;
use dbh_helper;
use DBI;
use DBD::mysql;
my $THIS_DIR = "$FindBin::Bin";
$THIS_DIR =~ s/^(.*?)\/*$/$1/;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    save mysql databases

	populates
		analysis
		exon
		exon_stable_id
		exon_transcript
		gene
		gene_description
		gene_stable_id
		interpro
		protein_feature
		seq_region
		transcript
		transcript_stable_id
		translation
		translation_stable_id
		xref

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE

#_________________________________________________________________________________________

#	retrieve_and_load_mysql_tables

#		retrieve mysql data from ENSEMBL

#_________________________________________________________________________________________
sub retrieve_and_load_mysql_tables($\@$$)
{
	my ($orig_mysql_dbh, $files, $ens_db_name, $ens_db_dir) = @_;

	print STDERR "\tLoading $ens_db_name\n";


	#
	#	create database
	#
	print STDERR "\tCreate database $ens_db_name\n";
	if (!$orig_mysql_dbh->do("CREATE DATABASE $ens_db_name"))
	{
		print STDERR "\tWARNING:\n\t\t$ens_db_name created previously\n\t\t",
					$orig_mysql_dbh->{'mysql_error'}, "\n\n";
		if ($orig_mysql_dbh->{'mysql_error'} !~ /database exists/)
		{
			# failed for some other reason
			die $@;
		}
	}

	#
	#	connect to new database
	#
	print STDERR "\t\tConnecting to dbi:mysql:$ens_db_name:$ens_db_host $ens_db_user\n";
	my $mysql_dbh = DBI->connect("dbi:mysql:$ens_db_name:$ens_db_host", "$ens_db_user", '',
							{
								RaiseError => 1,
								AutoCommit => 1,
								PrintError => 0
							}
						)
	or die "Database connection not made: DBI::errstr()";


	my $curr_tables = $mysql_dbh->selectrow_array("SHOW TABLES");
	if (!$curr_tables)
	{
		#
		#	create schema
		#
		my $file = "$ens_db_name.sql.gz";
		system("wget -q $ens_db_dir/$file -P $dir_pipeline_temp -o $dir_pipeline_temp/ensembl_download.wget.log") &&
			die "Error:\n\tCouldn't retrieve the sql schema file $ens_db_dir/$file";
	
		#
		#	unzip
		#
		print STDERR "\tUnzipping $file...\n";
		system("gunzip $dir_pipeline_temp/$file");
		print STDERR "\tCreating SQL schema...\n";
		system("mysql -s -h $ens_db_host $ens_db_name < $dir_pipeline_temp/$ens_db_name.sql 2>/dev/null");
		unlink(glob("$dir_pipeline_temp/*.sql"));
	}

	

	


	#
	#	retrieve files
	#
	print STDERR "\tRetrieving files...\n";
	for my $file (@$files)
	{
		my $table_name = $file;
		$table_name =~ s/\..*//;

		#
		#	Is download necessary
		#		only add to table if no values already
		#
		my $cnt_curr_rows = 0;
		eval 
		{
			$cnt_curr_rows
				= dbh_get_single_value($mysql_dbh, "SELECT COUNT(*) FROM $table_name" );
		};
		if ($@)
		{
			# failed because no table in schema. Just ignore
			if ($@ =~ /Table.*doesn't exist/)
			{
				print STDERR "\tWARNING:\n\t\tNo mysql data for $table_name\n";
				next;
			}
			# failed for some other reason
			die $@;
		}

		# data already present
		next if ($cnt_curr_rows);

		#
		#	download
		#
		#print STDERR "wget -q $ens_db_dir/$file\n";
		system("wget -q $ens_db_dir/$file -P $dir_pipeline_temp  -o $dir_pipeline_temp/ensembl_download.wget.log") && 
			die "Error:\n\tCouldn't retrieve $ens_db_dir/$file";
		#
		#	unzip
		#
		print STDERR "\t\tUnzipping $file...\n";
		system("gunzip $dir_pipeline_temp/$file");

		#
		#	enter into mysql
		#
		print STDERR "\t\tImporting data...\n";
		system("mysqlimport -s -L -h $ens_db_host $ens_db_name $dir_pipeline_temp/$table_name.txt.table");

		#
		#	clean up
		#
		unlink(glob("$dir_pipeline_temp/$table_name.txt.table"));
	}
	$mysql_dbh->disconnect();
}



#_________________________________________________________________________________________

#	retrieve_ensembl_per_taxon_sql_tables

#		retrieve per taxon sql tables from ENSEMBL

#_________________________________________________________________________________________
sub retrieve_ensembl_per_taxon_sql_tables($$)
{
	my ($mysql_dbh, $taxon) = @_;
	#
	#	ftp directory
	#
	my $ens_db_dir = $taxon->[ENS_FTP_SQL_DIR];


	#
	#	ftp directory
	#

	#	ftp://ftp.ensembl.org/pub/human-27.35a/data/mysql
	#my $ens_ftp_dir_name = $taxon->[ENS_COMMONTAXON] . "-" . $taxon->[ENS_VERSION];
	#$ens_ftp_dir_name =~ tr/_/./;
	#my $ens_db_dir = "ftp://ftp.ensembl.org/pub/$ens_ftp_dir_name/data/mysql/$ens_db_name";


	#
	#	files
	#
	my @files = 
				qw(
					analysis.txt.table.gz
					exon.txt.table.gz
					exon_stable_id.txt.table.gz
					exon_transcript.txt.table.gz
					gene.txt.table.gz
					gene_description.txt.table.gz
					gene_stable_id.txt.table.gz
					interpro.txt.table.gz
					protein_feature.txt.table.gz
					seq_region.txt.table.gz
					transcript.txt.table.gz
					transcript_stable_id.txt.table.gz
					translation.txt.table.gz
					translation_stable_id.txt.table.gz
					xref.txt.table.gz);

	my $ens_db_name = get_enscore_db_name($taxon);
	retrieve_and_load_mysql_tables($mysql_dbh, @files, $ens_db_name, $ens_db_dir);
}

sub retrieve_ensmart_per_taxon_sql_tables($\@)
{
	my ($mysql_dbh, $db_param) = @_;
	print STDERR "\tRetrieving from Ensembl...\n";


	#
	#	ftp directory
	#
	my $ens_db_dir = get_ensmart_ftp_directory_name();


	my @files;
	for my $taxon_data(@$db_param)
	{
		#
		#	species name in table
		#
		my $ens_table_prefix = $taxon_data->[ENS_TAXON_ABBREV] .'_';

		#
		#	files
		#
		if ($ens_mart_version > 27)
		{
			push(@files, map{$ens_table_prefix.$_}
							qw(
								gene_ensembl__transcript__main.txt.table.gz
								gene_ensembl_structure__structure__main.txt.table.gz
								go_biol_process__go_biol_process__main.txt.table.gz
								go_cell_location__go_cell_location__main.txt.table.gz
								go_mol_function__go_mol_function__main.txt.table.gz
								gene_ensembl__gene__main.txt.table.gz
								));
		}
		else
		{
			push(@files, map{$ens_table_prefix.$_}
							qw(
								gene_ensembl__transcript__main.txt.table.gz
								gene_ensembl__structure__dm.txt.table.gz
								gene_ensembl__go_biol_process__look.txt.table.gz
								gene_ensembl__go_cell_location__look.txt.table.gz
								gene_ensembl__go_mol_function__look.txt.table.gz
								gene_ensembl__gene__main.txt.table.gz
								));
		}
	}

	my $ens_db_name = get_ensmart_db_name();
	retrieve_and_load_mysql_tables($mysql_dbh, @files, $ens_db_name, $ens_db_dir);
}

#_________________________________________________________________________________________

#	retrieve_ensembl_multi_species_sql_tables

#		retrieve compara sql tables from ENSEMBL

#_________________________________________________________________________________________
sub retrieve_ensembl_multi_species_sql_tables($)
{
	my ($mysql_dbh) = @_;
	#
	#	ftp directory
	#
	my $ens_db_dir = get_enscompara_ftp_directory_name();

	#
	#	files
	#
	my @files = 
					qw(
						genome_db.txt.table.gz
						method_link_species_set.txt.table.gz
						method_link.txt.table.gz
						member.txt.table.gz
						homology_member.txt.table.gz
						homology.txt.table.gz
						);

	my $ens_db_name = get_enscompara_db_name();
	retrieve_and_load_mysql_tables($mysql_dbh, @files, $ens_db_name, $ens_db_dir);
}









#
#	Get DB parameters
#
my $dbh = connect_to_panda();
use get_psql_time;
my $start_time = get_psql_time($dbh);


my $mysql_dbh = DBI->connect("dbi:mysql::$ens_db_host", "$ens_db_user", '',
						{
							RaiseError => 0,
							AutoCommit => 1,
							PrintError => 0
						}
					)
or die "Database connection to dbi:mysql::$ens_db_host not made: DBI::errstr()";





#
#	per taxon sql
#
my @ensemb_db_param = get_ensembl_taxon_data();
{
	for my $taxon_data(@ensemb_db_param)
	{
	
	
		#
		#	Retrieve from ENSEMBL
		#
		print STDERR "\tRetrieving from Ensembl...\n";
		#	UNCOMMENT
		retrieve_ensembl_per_taxon_sql_tables($mysql_dbh, $taxon_data);
	
	}

	#
	#	Retrieve Ensmart
	#
	retrieve_ensmart_per_taxon_sql_tables($mysql_dbh, @ensemb_db_param);

}

	#
	# Retrieve compara
	#
	retrieve_ensembl_multi_species_sql_tables($mysql_dbh);


$mysql_dbh->disconnect();


my %taxa_data = get_taxon_data();
#
#	Download peptide and cDNA files if necessary
#
for my $ens_taxon_data(@ensemb_db_param)
{
	my $sequence_dir = $taxa_data{$ens_taxon_data->[TAXON]}[SEQ_DIR];
	
	
#	COMMENT	
	#A# last;	#A#
	#
	#	create directory to hold sequences
	#
	die "Error:\n\t$ens_taxon_data->[TAXON] specified in ENSEMBL_DB_SOURCES but not found in TAXA_PARAMETERS" unless defined $sequence_dir;
	unless (-d "$sequence_dir")
	{
		# delete if is a file
		if (-f "$sequence_dir")
		{
			unlink "$sequence_dir" or die;
		}
		mkdir "$sequence_dir" or die "Error: \n\tCan't make $sequence_dir\n";
	}


	#
	#	create directory to hold dna
	#
	unless (-d "$sequence_dir/dna")
	{
		# delete if is a file
		if (-f "$sequence_dir/dna")
		{
			unlink "$sequence_dir/dna" or die;
		}
		mkdir "$sequence_dir/dna" or die "Error: \n\tCan't make $sequence_dir/dna\n";
	}

	#
	#	download dna
	#
	{
		print STDERR "\tDownloading $ens_taxon_data->[TAXON] DNA from Ensembl...\n";
		system("wget -N \"$ens_taxon_data->[ENS_FTP_FASTA_DIR]/dna/*.dna.chromosome.*.fa.gz\" -P $sequence_dir/dna  -o $dir_pipeline_temp/ensembl_download.wget.log");
		open LOG, "$dir_pipeline_temp/ensembl_download.wget.log" or die;
		my @files;
		while (<LOG>)
		{
			next unless /`(.*dna.chromosome.*fa.gz)' saved/;
			push(@files, $1);
			print STDERR "$1\n";
		}

		#gunzip dna
		print STDERR "\tUnzipping ", scalar @files, " files from Ensembl...\n" if @files;
		for (@files)
		{
			/(chromosome.*fa)/;
			print STDERR "\tUnzipping $1 from Ensembl...\n";
			my $file_name = "$sequence_dir/dna/$1";
			system("gunzip $_ --stdout 1> $file_name 2>/dev/null");

			# replace line endings
			{
				my $acc_line = '';
				my $seq = '';
				{
					open FA, "$file_name" or die "Error:\n\tCould not open '$file_name'\n";
					$acc_line = <FA>;
					local $/ = undef;
					$seq = <FA>;
					$seq =~ tr/\n//d;
				}
				{
					open FA, ">$file_name" or die "Error:\n\tCould not open '$file_name'\n";
					print FA $acc_line,$seq,"\n";
				}
			}

			
		}
	}
	
	unless (-f "$sequence_dir/cdna.fa")
	{
		print STDERR "\tDownloading $ens_taxon_data->[TAXON] cDNA from Ensembl...\n";
		system("wget $ens_taxon_data->[ENS_FTP_FASTA_DIR]/cdna/*.cdna.fa.gz -P $sequence_dir 2>/dev/null");
		print STDERR "\tUnzipping cDNA from Ensembl...\n";
		system("gunzip $sequence_dir/*.cdna.fa.gz --stdout 1> $sequence_dir/cdna.fa 2>/dev/null");
#		unlink(glob("$sequence_dir/*.cdna.fa.gz"));
	}
	unless (-f "$sequence_dir/prot.fa")
	{
		print STDERR "\tDownloading $ens_taxon_data->[TAXON] peptides from Ensembl...\n";
		system("wget $ens_taxon_data->[ENS_FTP_FASTA_DIR]/pep/*.pep.fa.gz -P $sequence_dir 2>/dev/null");
		print STDERR "\tUnzipping peptides from Ensembl...\n";
		system("gunzip $sequence_dir/*.pep.fa.gz --stdout 1> $sequence_dir/prot.fa 2>/dev/null");
#		unlink(glob("$sequence_dir/*.pep.fa.gz"));
	}
}
unlink ("$dir_pipeline_temp/ensembl_download.wget.log");


log_pipeline_stage($dbh, 'NULL', 5, 'Get sequences and identifiers', $start_time);
$dbh->disconnect();


print STDERR "\tCompleted\n";





